<?php
/**
 * Provide special functions for members of lets manager
 * Add exclusion on association account
 * @package    letsManager
 * @subpackage model
 * @author     Jerome Fouilloy
 * @version    SVN: $Rev$
 */
class LetsMemberTable extends MemberTable
{
  const MEMBER_LEVEL_ADMIN = 5;
  const MEMBER_LEVEL_MANAGER = 2;
  const MEMBER_LEVEL_NORMAL = 1;

  /**
   * Retrieve list of Member who belong to association $id.
   * Used in export feature
   *
   * @param   integer   $id
   * @return  array of Members
   */
  public static function getEnabledForAssociation($asso_id,$include_address_hidden = true,$order_by = 'username')
  {
    $q = self::getQueryEnabledForAssociation($asso_id,$include_address_hidden,$order_by);
    if($order_by == 'username')
    {
      $q->orderBy('(m.User.username +0) ASC');
    }
    else if($order_by == 'firstname')
    {
      $q->orderBy('m.User.first_name ASC');
    }
    else if($order_by == 'lastname')
    {
      $q->orderBy('m.User.last_name ASC');
    }
    return $q->execute();
  }
  
  public static function getQueryEnabledForAssociation($asso_id)
  {
    $q = parent::getQueryEnabledForAssociation($asso_id);
    self::addExcludeMemberAssociation($asso_id,$q);
    return $q;
  }
  
  public static function doCount($asso_id = -1)
  {
    if($asso_id == -1)
    {
      return parent::doCount();
    }
    $q = self::getQueryEnabledForAssociation($asso_id);
    self::addExcludeMemberAssociation($asso_id,$q);
    return $q->count();
  }

  public static function getHavingEmailForAssociation($asso_id)
  {
   	$q = self::getQueryEnabledForAssociation($asso_id);
   
   	$q->andWhere('m.User.email IS NOT NULL')
   	->andWhere('m.User.email != ""');
   	return $q->execute();
  }
  
  public static function getPendingMembers($asso_id)
  {
    $q = Doctrine_Query::create()
    ->select('m.*')
    ->from('Member m')
    ->where('m.association_id = ?', $asso_id)
    ->andWhere('state = ?', self::STATE_PENDING);
    self::addExcludeMemberAssociation($asso_id,$q);

    return $q->execute();
  }
  /**
   * Get newest members
   * TODO: add association_id
   * @param $asso_id
   * @param $limit
   */
  public static function getNewestGuardUsers($asso_id,$limit = 5)
  {
    $q = sfGuardUserTable::getInstance()->createQuery()
      ->from('sfGuardUser m')
      ->select('m.*')
    //  ->leftJoin('Member m ON m.id = u.id')
    //  ->where('m.association_id = ?', $asso_id)
    //  ->andWhere('m.state = ?', self::STATE_ENABLED)
      ->where('m.is_active = 1')
      ->orderBy('created_at DESC')
      ->limit($limit);
   // self::addExcludeMemberAssociation($asso_id,$q);

    return $q->execute();
    
  }
  /**
   * Exclude member association in query
   * @param integer $asso_id
   * @param Doctrine_Query $q
   * @return Doctrine_Query
   */
  private static function addExcludeMemberAssociation($asso_id,Doctrine_Query $q)
  {
    // exclude special account for association money
    $memberAssociation = LetsConfiguration::memberIdForAssociation($asso_id);
    if($memberAssociation > 0)
    {
      $q->andWhere('m.id != ?',$memberAssociation);
    }
    return $q;
  }
  public static function getQuerySearch($params)
  {
    $q = parent::getQuerySearch($params);
    if(!isset($params['association_id']))
    {
      throw new InvalidArgumentException('You must provide the association_id argument');
    }
    self::addExcludeMemberAssociation($params['association_id'],$q);
     
    return $q;
  }
  /**
   * Get members match with query
   * @param string $query text query for search in firstname, lastname, username of all members in association
   * @param integer $limit limit number of results
   * @param integer $associationId, if equals -1 ignore argument
   * @param boolean $hideMemberAssociation true to hide association member in results
   * @param string  $order_by
   * @return Doctrine_Collection of Member
   */
  static public function magicSearch($query, $limit, $asso_id, $hide_member_association = true, $order_by = 'username')
  {
    $params = array('association_id' => $asso_id,
                    'state' => self::STATE_ENABLED,
                    'magic'=> $query );
    // we must order username as integer and not string
    if($order_by != 'username')
    {
      $params['order_by'] = $order_by;
    }
    $q = parent::getQuerySearch($params);
    if (isset ($params['magic']) && $params['magic'] != "")
    {
      $q->orWhere('m.User.username = ?',$params['magic']);
    }
    if ($hide_member_association)
    {
      $q->andWhere('m.id != ?',LetsConfiguration::memberIdForAssociation($asso_id));
    }
    if($limit > 0)
    {
      $q->limit($limit);
    }
    if ($order_by == 'username')
    {
      $q->orderBy('(m.User.username +0)');
    }
    return $q->execute();
  }
  /**
   * Get member name and username
   * @param integer $member_id
   * @return string or null firstname lastname (username) if user found or null
   */
  public static function getNameById($member_id,$show_username = false)
  {
    $q = Doctrine_Query::create()
    ->select("CONCAT(user.last_name,' ',user.first_name". ($show_username ? ",' (',user.username,')'" : "" ) .") as name")
    ->from('sfGuardUser user, Member m')
    ->where('user.id = ?', $member_id)
    ->andWhere('m.state = ?', self::STATE_ENABLED);
    $res = $q->fetchArray();
    return (count($res)? $res[0]['name'] : null);
  }
  
  /**
   * Return 1 if standard member, 2 if ca member, 5 admin
   * @param Member $member
   */
  public static function getLevelRightForMemberId($member_id)
  {
    // This function is used only for menu bar
    $member = self::getById($member_id);
    // member not found
    if($member == null)
    {
      return null;
    }
    
    // TODO: change that
    $names = $member->getAclGroupNames();
    if(count($names) > 1)
    {
      return self::MEMBER_LEVEL_MANAGER;
    }
    else{
      $name = strtolower(trim($names[0]));
      if(stristr($name,'membre ca'))
      {
        return self::MEMBER_LEVEL_MANAGER;
      }
      else if(stristr($name,'membre'))
      {
        return self::MEMBER_LEVEL_NORMAL;
      }
      else if(stristr($name,'administrateurs'))
      {
        return self::MEMBER_LEVEL_ADMIN;
      }
    }
  }
  /**
   * Create login with maximum member number incremented by 1, use max on username colum
   * @param string $prefix
   */
  public static function generateNextLogin($asso_id,$prefix='')
  {
    // +1 > 1 avoid to get username without number because MySQL "'test4' + 1 " = 1 (ignore string value in sum and use 0)
    // and SQL " '1'+1 "= 2
    $q = Doctrine_Query::create()->select('MAX(m.User.username+0) as maxi')->from('Member m')
        ->andWhere('m.association_id = ?',$asso_id)->andWhere('m.User.username + 1 > 1');
        
    $res = $q->fetchArray();
    if(count ($res))
    {
      $nextId = intval($res[0]['maxi']);
    }
    else{
      $nextId =1;
    }
    $nextId++;
    return $prefix.$nextId;
  }
  
  public static function generatePassword($length =8)
  {
    $password = "";
    $possible = "0123456789bcdfghjkmnpqrstvwxyz_+";

    for ($i = 0; $i < $length; ++$i)
    {
      $char = substr($possible, mt_rand(0, strlen($possible) - 1), 1);
      
      if (! strstr($password, $char))
      {
        $password .= $char;
      }
      else
      {
        if ($length < strlen($possible))
        {
          $i--;
        }
      }
    }

    return $password;
  }
  /**
   * Get string list with name and id of members can manage exchanges others
   * @return string name (id), name2 (id2) string list
   */
  public static function stringListExchangeManagers()
  {
    $members = MemberTable::getMembersForAclRight(LetsManagerRights::MANAGE_EXCHANGE_OTHERS,false);
    $str = '';
    foreach($members as $member)
    {
      $str = $member->getName().' ('.$member->getId().'), ';
    }
    return $str;
  }
  /**
   * Generate html for member marker on map
   * @param Member $member
   * @param sfAction $action current action
   * @return string html content for member
   */
  public static function formatInfosGmap(Member $member,sfAction $action)
  {

    $phone = '';
    if(trim($member->getPhoneHome()) != '')
    {
      $phone = $member->getPhoneHomeFormatted();
    }
    if(trim($member->getPhoneMobile()) != '')
    {
      if($phone != '')
      {
        $phone .= ' ou ';
      }
      $phone .= $member->getPhoneMobileFormatted();
    }
    $email = ($member->hasEmail() ? "<a href='mailto:".$member->getEmail()."'>".$member->getEmail()."</a>" : '');
    $name = $member->getName() .' ('.$member->getUsername().')';
    $link = $action->generateUrl("lets_user_show",array('id'=> $member->getId()),true);
    
    $infos = '<div style="overflow: hidden"><a href="'.$link.'" target="_blank"><b>'. $name .'</b></a><br/>'.
    $member->getStreet(). '<br/>' . $member->getStreet2(). '<br/>' .$member->getZipcode().' '. $member->getCity() .'<br/>'.
    $phone.'<br/>'.$email.'</div>';
    return $infos;
  }
}